# -*- coding: utf-8 -*-
"""
从tushare下载历史数据，复权数据，基本面数据
"""
import config
import pandas as pd
import sys
import imp
import datetime
import time
import tushare as ts

# 导入自定义模块
model_path=config.get_model_path()
sys.path.append(model_path)
import pyoracle as orcl
imp.reload(orcl)
import get_time as gt
imp.reload(gt)
#-----------------------------------------
# 导入全局参数
user, pwd, ip, db, tns = config.get_orcl_conn_var()
# conn = orcl.oracle_conn(user=user, pwd=pwd, ip=ip, db=db)

#-------测试--------------------------------
# 中车
# code='601766'
# start = '2015-06-01'
# start = today()
# end= today()
# end=None
#-------测试--------------------------------

def take_hist_stock(conn,code,start=None, end=None):
    '''获取指定时间段的历史数据，如果不指定，则全部'''
    df = ts.get_hist_data(code,start=start,end=end)
    # 添加时间列,code列
    df['dates']=df.index
    df['code']=code
    # 选择需要的列
    df2 = df[['code','dates','open','high','close','low','volume','price_change','p_change','ma5','ma10','ma20','v_ma5','v_ma10','v_ma20','turnover']]
    # 入库
    table_name='stock'
    time_col = ['dates']
    orcl.df_into_oracle(conn,table=table_name,df=df2,time_col=time_col,types='insert')

def take_today_stock(conn):
    '''获取今天的数据，注意，有些数据需要转成万为单位。返回的是全部股票数据'''
    df = ts.get_today_all()
    # 增加时间列
    df['dates']=gt.today(has_line=1)
    # df[df['code']==code]
    # 成交量转成万手为单位
    df['volume'] = df['volume'].apply(lambda s:round(s/10000,2))
    # 成交额转成万元为单位
    df['amount'] = df['amount'].apply(lambda s:round(s/10000,2))
    # 市场资本额
    df['mktcap'] = df['mktcap'].apply(lambda s:round(s/10000,2))
    # nmc
    df['nmc'] = df['nmc'].apply(lambda s:round(s/10000,2))
    # 选择需要的列入库
    cols = ['code','name','dates','changepercent','trade','open','high','low','settlement','volume','turnoverratio','amount','per','pb','mktcap','nmc']
    df2 = df[cols]
    table_name='stock_now'
    time_col = ['dates']
    orcl.df_into_oracle(conn,table=table_name,df=df2,time_col=time_col,types='insert')

def take_qfq_stock(conn,code,start=None, end=None):
    '''获取前复权数据，注意，有些数据需要转成万为单位'''
    df = ts.get_h_data(code,start=start,end=end)
    df['dates']=df.index
    df['code']=code
    # volume转成手，和历史数据保持一致
    # amount转成万元为单位
    df['volume'] = df['volume'].apply(lambda s:round(s/100,2))
    df['amount'] = df['amount'].apply(lambda s:round(s/10000,2))
    # 选择需要的列
    cols = ['code','dates','open','high','close','low','volume','amount']
    df2 = df[cols]
    # 入库
    table_name='stock_qfq'
    time_col = ['dates']
    orcl.df_into_oracle(conn,table=table_name,df=df2,time_col=time_col,types='insert')

def take_basic(conn):
    '''获取基本数据，主要是市盈率市净率等'''
    df = ts.get_stock_basics()
    # df.loc[code]
    df['code']=df.index
    df['dates']=gt.today(has_line=1)
    # 选择需要的列
    cols=['name', 'code', 'dates', 'industry', 'area', 'pe', 'outstanding', 'totals', 'totalAssets', 'liquidAssets', 'fixedAssets', 'reserved', 'reservedPerShare', 'esp', 'bvps', 'pb', 'timeToMarket']
    df2 = df[cols]
    # 入库
    table_name='basic'
    time_col = ['dates']
    orcl.df_into_oracle(conn,table=table_name,df=df2,time_col=time_col,types='insert')

def get_all_code():
    '''获取全部股票代码'''
    # 获取实时行情
    df = ts.get_today_all()
    all_code = list(df.code)
    return all_code

def take_ycz_all_qx_data(conn):
    '''将预测者网的历史全息数据导入数据库'''
    path1 = r"F:\stock_data\overview-data-sh"
    path2 = r"F:\stock_data\overview-data-sz"
    path = path2
    all_file = os.listdir(path)
    for file in all_file:
        if '.csv' not in file:  # 如果是数据文件，而不是文件夹，就入库
            continue
        file = os.path.join(path,file)
        data = pd.read_csv(file,encoding='GB18030')
        data.columns = ['code', 'name', 'dates', 'industry', 'notion', 'area', 'open', 'high', 'low', 'close', 'hfq', 'qfq', 'p_change', 'volume', 'amount', 'turnover', 'traded_value', 'total_value', 'up_stop', 'down_stop', 'PE_TTM', 'PS_TTM', 'PC_TTM', 'PB', 'MA_5', 'MA_10', 'MA_20', 'MA_30', 'MA_60', 'ma_fork', 'MACD_DIF', 'MACD_DEA', 'MACD_MACD', 'macd_fork', 'KDJ_K', 'KDJ_D', 'KDJ_J', 'kdj_fork', 'bulin_m', 'bulin_up', 'bulin_d', 'psy', 'psyma', 'rsi1', 'rsi2', 'rsi3', 'p_amplitude', 'volume_ratio']
        # 处理空值和inf等特殊字符串
        data2 = data.applymap(lambda s:str(s))
        data2 = data2.applymap(lambda s:s.replace('nan',''))
        data2 = data2.applymap(lambda s:s.replace('inf',''))
        # 排序，取最近的400天数据
        if data2['dates'].max() >='2015-12-31':  # 退市的不计
            data3 = data2.sort_values('dates')[-250:]
        # 入库
        table_name='stock_qx'
        time_col = ['dates']
        orcl.df_into_oracle(conn,table=table_name,df=data3,time_col=time_col,types='insert')
        logger.info(file+' 入库成功')






def main(logger=''):
    # 导入日志
    if not logger:
        config.set_log()
        logger = config.get_log()
    logger.info('---------------------------------------')
    # 获取数据库连接
    conn = orcl.oracle_conn(user=user, pwd=pwd, ip=ip, db=db)
    
    # 要先删除今天的数据
    #--------------------------------------------
    # 获取所有股票代码,从小到大排序
    all_code = get_all_code()
    all_code.sort()
    # 使用循环获取每个股票的历史数据，导入数据库
    # 注意成交量是单位是手(=股数/100)
    # 一年半共86万数据，耗时16分钟
    # 先删除今天的数据
    orcl.delete_today_data(conn,table='stock',code='',date=gt.today())
    # start = '2015-01-01' start=None
    # end = '2014-12-31'
    start = gt.today(has_line=1)
    t1=time.clock()
    for code in all_code:
        # take_hist_stock(conn,code,start=start, end=None)
        take_hist_stock(conn,code,start=start, end=end)
        logger.info('完成 '+code+' 的历史数据下载')
    sp = '完成下载,入库所有股票的指定时间段的历史数据，耗时：'+str(round((time.clock()-t1),2))+' 秒'
    logger.info(sp)
    #--------------------------------------------
    # 下载前复权数据
    # 注意成交量单位是股，需要转成手，和stock保持一致
    # 先删除今天的数据
    orcl.delete_today_data(conn,table='stock_qfq',code='',date=gt.today())
    # start = '2015-01-01'
    start = gt.today(has_line=1)
    t1=time.clock()
    for code in all_code:
        take_qfq_stock(conn,code,start=start, end=None)
        logger.info('完成 '+code+' 的前复权数据下载')
    sp = '完成下载,入库所有股票的指定时间段的前复权数据，耗时：'+str(round((time.clock()-t1),2))+' 秒'
    logger.info(sp)
    #--------------------------------------------
    # 下载基本面数据
    # 先删除今天的数据
    orcl.delete_today_data(conn,table='basic',code='',date=gt.today())
    take_basic(conn)
    logger.info('完成基本面数据下载')
    

if __name__ == '__main__':
    main()






























